| 15.5: Few Final Tidbits | 您所在的位置：网站首页 › aa school ddl › 15.5: Few Final Tidbits |
15.5: Few Final Tidbits
|
User Defined Types
User defined types are always based on system-supplied data type. They can enforce data integrity and they allow nulls. To create a user-defined data type in SQL Server, choose types under “Programmability” in your database. Next, right click and choose ‘New’ –>‘User-defined data type’ or execute the sp_addtype system stored procedure. After this, type: sp_addtype ssn, ‘varchar(11)’, ‘NOT NULL’ This will add a new user-defined data type called SIN with nine characters. In this example, the field EmployeeSIN uses the user-defined data type SIN. CREATE TABLE SINTable ( EmployeeID INT Primary Key, EmployeeSIN SIN, CONSTRAINT CheckSIN CHECK (EmployeeSIN LIKE ‘ [0-9][0-9][0-9] – [0-9][0-9] [0-9] – [0-9][0-9][0-9] ‘) ) ALTER TABLEYou can use ALTER TABLE statements to add and drop constraints. ALTER TABLE allows columns to be removed. When a constraint is added, all existing data are verified for violations.In this example, we use the ALTER TABLE statement to the IDENTITY property to a ColumnName field. USE HOTEL GO ALTER TABLE tblHotel ADD CONSTRAINT unqName UNIQUE (Name)Use the ALTER TABLE statement to add a column with the IDENTITY property such as ALTER TABLE TableName. ADD ColumnName int IDENTITY(seed, increment) DROP TABLEThe DROP TABLE will remove a table from the database. Make sure you have the correct database selected. DROP TABLE tblHotelExecuting the above SQL DROP TABLE statement will remove the table tblHotel from the database. Key Terms DDL: abbreviation for data definition language DML: abbreviation for data manipulation language SEQUEL: acronym for Structured English Query Language; designed to manipulate and retrieve data stored in IBM’s quasi-relational database management system, System R Structured Query Language (SQL): a database language designed for managing data held in a relational database management system Exercises Using the information for the Chapter 9 exercise, implement the schema using Transact SQL (show SQL statements for each table). Implement the constraints as well. Create the table shown here in SQL Server and show the statements you used. Table: Employee ATTRIBUTE (FIELD) NAME DATA DECLARATION EMP_NUM CHAR(3) EMP_LNAME VARCHAR(15) EMP_FNAME VARCHAR(15) EMP_INITIAL CHAR(1) EMP_HIREDATE DATE JOB_CODE CHAR(3) Table \(\PageIndex{1}\): Copy and Paste Caption here. (Copyright; author via source) Having created the table structure in question 2, write the SQL code to enter the rows for the table shown in Table \(\PageIndex{1}\).
Figure \(\PageIndex{2}\): Employee table with data. by A. Watt.
Use Figure \(\PageIndex{2}\) to answer questions 4 to 10. Write the SQL code to change the job code to 501 for the person whose personnel number is 107. After you have completed the task, examine the results, and then reset the job code to its original value. Assuming that the data shown in the Employee table have been entered, write the SQL code that lists all attributes for a job code of 502. Write the SQL code to delete the row for the person named William Smithfield, who was hired on June 22, 2004, and whose job code classification is 500. (Hint: Use logical operators to include all the information given in this problem.) Add the attributes EMP_PCT and PROJ_NUM to the Employee table. The EMP_PCT is the bonus percentage to be paid to each employee. Using a single command, write the SQL code that will enter the project number (PROJ_NUM) = 18 for all employees whose job classification (JOB_CODE) is 500. Using a single command, write the SQL code that will enter the project number (PROJ_NUM) = 25 for all employees whose job classification (JOB_CODE) is 502 or higher. Write the SQL code that will change the PROJ_NUM to 14 for those employees who were hired before January 1, 1994, and whose job code is at least 501. (You may assume that the table will be restored to its original condition preceding this question.)Also see Appendix C: SQL Lab with Solution |
【本文地址】